/*
 * MIT License
 *
 * Copyright (c) 2023 北京凯特伟业科技有限公司
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */
// Copyright tang.  All rights reserved.
// https://gitee.com/inrgihc/dbswitch
//
// Use of this source code is governed by a BSD-style license
//
// Author: tang (inrgihc@126.com)
// Date : 2020/1/2
// Location: beijing , china
/////////////////////////////////////////////////////////////
package com.je.meta.service.dbswitch.service.impl.database;

import com.google.common.base.Strings;
import com.je.common.base.constants.table.TableType;
import com.je.ibatis.extension.plugins.pagination.Page;
import com.je.meta.model.database.ColumnDescription;
import com.je.meta.model.database.ColumnMetaData;
import com.je.meta.model.database.TableDescription;
import com.je.meta.model.database.type.ProductTypeEnum;
import com.je.meta.service.dbswitch.common.constant.Const;
import com.je.meta.service.dbswitch.service.AbstractDatabase;
import com.je.meta.service.dbswitch.service.IDatabaseInterface;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.util.*;

/**
 * 支持Oracle数据库的元信息实现
 * <p>
 * 备注：
 * <p>
 * （1）Oracle12c安装教程：
 * <p>
 * 官方安装版：https://www.w3cschool.cn/oraclejc/oraclejc-vuqx2qqu.html
 * <p>
 * Docker版本：http://www.pianshen.com/article/4448142743/
 * <p>
 * https://www.cnblogs.com/Dev0ps/p/10676930.html
 * <p>
 * (2) Oracle的一个表里至多只能有一个字段为LONG类型
 *
 * @author tang
 */
public class DatabaseOracleImpl extends AbstractDatabase implements IDatabaseInterface {

    private static final String SHOW_CREATE_TABLE_SQL =
            "SELECT DBMS_METADATA.GET_DDL('TABLE','%s','%s') FROM DUAL ";
    private static final String SHOW_CREATE_VIEW_SQL =
            "SELECT DBMS_METADATA.GET_DDL('VIEW','%s','%s') FROM DUAL ";

    private static final String GET_DEFAULT_TABLESPACE = "SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='%s'";

    public DatabaseOracleImpl() {
        super("oracle.jdbc.driver.OracleDriver");
    }

    @Override
    public ProductTypeEnum getDatabaseType() {
        return ProductTypeEnum.ORACLE;
    }

    @Override
    public List<String> querySchemaList(Connection connection) {
        List<String> result = new ArrayList<>();
        try (PreparedStatement ps = connection.prepareStatement(String.format(GET_DEFAULT_TABLESPACE, connection.getMetaData().getUserName()));
             ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                result.add(rs.getString("DEFAULT_TABLESPACE"));
            }
            return result;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Page queryTableList(Connection connection, String schemaName, String productId, String fuzzyValue, int intPage
            , int intLimit) {
        Page page = new Page(intPage, intLimit);
        List<TableDescription> ret = new ArrayList<>();
        String sql = String.format("SELECT \"OWNER\",\"TABLE_NAME\",\"TABLE_TYPE\",\"COMMENTS\" "
                + "FROM all_tab_comments where \"OWNER\"='%s'", schemaName);
        List<String> metaTablesCode = getJeMetaTablesCode(productId);
        try (PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                TableDescription td = new TableDescription();
                td.setSchemaName(rs.getString("OWNER"));
                td.setTableCode(rs.getString("TABLE_NAME"));
                td.setTableName(rs.getString("COMMENTS"));
                String tableType = rs.getString("TABLE_TYPE").trim();
                if (metaTablesCode.contains(td.getTableCode().toUpperCase())) {
                    continue;
                }
                if (!isFuzzyValue(td, fuzzyValue)) {
                    continue;
                }
                if (tableType.equalsIgnoreCase("VIEW")) {
                    td.setTableType(TableType.VIEWTABLE);
                } else {
                    td.setTableType(TableType.PTTABLE);
                }
                ret.add(td);
            }
            return getPage(ret, intPage, intLimit);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public TableDescription queryTableMeta(Connection connection, String schemaName,
                                           String tableName) {
        TableDescription tableDescription = new TableDescription();
        String sql = String.format("SELECT \"OWNER\",\"TABLE_NAME\",\"TABLE_TYPE\",\"COMMENTS\" "
                + "FROM all_tab_comments where \"OWNER\"='%s' and TABLE_NAME='%s'", schemaName, tableName);
        try (PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();) {
            while (rs.next()) {
                TableDescription td = new TableDescription();
                td.setSchemaName(rs.getString("OWNER"));
                td.setTableCode(rs.getString("TABLE_NAME"));
                td.setTableName(rs.getString("COMMENTS"));
                String tableType = rs.getString("TABLE_TYPE").trim();
                if (tableType.equalsIgnoreCase("VIEW")) {
                    td.setTableType(TableType.VIEWTABLE);
                } else {
                    td.setTableType(TableType.PTTABLE);
                }
                tableDescription = td;
            }
            return tableDescription;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public String getTableDDL(Connection connection, String schemaName, String tableName) {
        String sql = String.format(SHOW_CREATE_TABLE_SQL, tableName, schemaName);
        try (Statement st = connection.createStatement()) {
            if (st.execute(sql)) {
                try (ResultSet rs = st.getResultSet()) {
                    if (rs != null && rs.next()) {
                        return rs.getString(1);
                    }
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return null;
    }

    @Override
    public String getViewDDL(Connection connection, String schemaName, String tableName) {
        String sql = String.format(SHOW_CREATE_VIEW_SQL, tableName, schemaName);
        try (Statement st = connection.createStatement()) {
            if (st.execute(sql)) {
                try (ResultSet rs = st.getResultSet()) {
                    if (rs != null && rs.next()) {
                        String exeSql = rs.getString(1);
                        exeSql = "CREATE VIEW " + tableName + " " + exeSql.substring(exeSql.indexOf("AS"));
                        return exeSql;
                    }
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        return null;
    }

    @Override
    public List<String> queryTablePrimaryKeys(Connection connection, String schemaName,
                                              String tableName) {
        // Oracle表的主键可以使用如下命令设置主键是否生效
        // 使主键失效：alter table tableName disable primary key;
        // 使主键恢复：alter table tableName enable primary key;
        Set<String> ret = new HashSet<>();
        String sql = String.format(
                "SELECT col.COLUMN_NAME FROM all_cons_columns col INNER JOIN all_constraints con \n"
                        + "ON col.constraint_name=con.constraint_name AND col.OWNER =con.OWNER  AND col.TABLE_NAME =con.TABLE_NAME \n"
                        + "WHERE con.constraint_type = 'P' and con.STATUS='ENABLED' and con.owner='%s' AND con.table_name='%s'",
                schemaName, tableName);
        try (PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();
        ) {
            while (rs.next()) {
                ret.add(rs.getString("COLUMN_NAME"));
            }

            return new ArrayList<>(ret);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public Map<String, List<String>> queryTablesPrimaryKeys(Connection connection, String schemaName) {
        // Oracle表的主键可以使用如下命令设置主键是否生效
        // 使主键失效：alter table tableName disable primary key;
        // 使主键恢复：alter table tableName enable primary key;
        Map<String, List<String>> result = new HashMap<>();
        String sql = String.format(
                "SELECT col.* FROM all_cons_columns col INNER JOIN all_constraints con \n"
                        + "ON col.constraint_name=con.constraint_name AND col.OWNER =con.OWNER  AND col.TABLE_NAME =con.TABLE_NAME \n"
                        + "WHERE con.constraint_type = 'P' and con.STATUS='ENABLED' and con.owner='%s' ",
                schemaName);
        try (PreparedStatement ps = connection.prepareStatement(sql);
             ResultSet rs = ps.executeQuery();
        ) {
            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                if (result.get(tableName) == null) {
                    List<String> keys = new ArrayList<>();
                    keys.add(rs.getString("COLUMN_NAME"));
                    result.put(tableName, keys);
                } else {
                    result.get(tableName).add(rs.getString("COLUMN_NAME"));
                }
            }
            return result;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<ColumnDescription> querySelectSqlColumnMeta(Connection connection, String sql) {
        String querySQL = String.format("SELECT * from (%s) tmp where ROWNUM<=1 ",
                sql.replace(";", ""));
        return this.getSelectSqlColumnMeta(connection, querySQL);
    }

    @Override
    protected String getTableFieldsQuerySQL(String schemaName, String tableName) {
        return String.format("SELECT * FROM \"%s\".\"%s\" ", schemaName, tableName);
    }

    @Override
    protected String getTestQuerySQL(String sql) {
        return String.format("explain plan for %s", sql.replace(";", ""));
    }

    @Override
    public String getFieldDefinition(ColumnMetaData v, List<String> pks, boolean useAutoInc,
                                     boolean addCr, boolean withRemarks) {
        String fieldname = v.getName();
        int length = v.getLength();
        int precision = v.getPrecision();

        StringBuilder retval = new StringBuilder(128);
        retval.append(" \"").append(fieldname).append("\"    ");

        int type = v.getType();
        switch (type) {
            case ColumnMetaData.TYPE_TIMESTAMP:
            case ColumnMetaData.TYPE_TIME:
                retval.append("TIMESTAMP");
                break;
            case ColumnMetaData.TYPE_DATE:
                retval.append("DATE");
                break;
            case ColumnMetaData.TYPE_BOOLEAN:
                retval.append("NUMBER(1)");
                break;
            case ColumnMetaData.TYPE_NUMBER:
            case ColumnMetaData.TYPE_BIGNUMBER:
                retval.append("NUMBER");
                if (length > 0) {
                    if (length > 38) {
                        length = 38;
                    }

                    retval.append('(').append(length);
                    if (precision > 0) {
                        retval.append(", ").append(precision);
                    }
                    retval.append(')');
                }
                break;
            case ColumnMetaData.TYPE_INTEGER:
                retval.append("INTEGER");
                break;
            case ColumnMetaData.TYPE_STRING:
                if (length >= AbstractDatabase.CLOB_LENGTH) {
                    retval.append("CLOB");
                } else {
                    if (length == 1) {
                        retval.append("NVARCHAR2(1)");
                    } else if (length > 0 && length < 2000) {
                        // VARCHAR2(size)，size最大值为4000，单位是字节；而NVARCHAR2(size)，size最大值为2000，单位是字符
                        retval.append("NVARCHAR2(").append(length).append(')');
                    } else {
                        retval.append("CLOB");// We don't know, so we just use the maximum...
                    }
                }
                break;
            case ColumnMetaData.TYPE_BINARY: // the BLOB can contain binary data.
                retval.append("BLOB");
                break;
            default:
                retval.append("CLOB");
                break;
        }

        if (addCr) {
            retval.append(Const.CR);
        }

        return retval.toString();
    }

    @Override
    public List<String> getTableColumnCommentDefinition(TableDescription td,
                                                        List<ColumnDescription> cds) {
        List<String> results = new ArrayList<>();
        if (StringUtils.isNotBlank(td.getTableName())) {
            results.add(String
                    .format("COMMENT ON TABLE \"%s\".\"%s\" IS '%s' ",
                            td.getSchemaName(), td.getTableCode(),
                            td.getTableName().replace("\"", "\\\"")));
        }

        for (ColumnDescription cd : cds) {
            if (StringUtils.isNotBlank(cd.getRemarks())) {
                results.add(String
                        .format("COMMENT ON COLUMN \"%s\".\"%s\".\"%s\" IS '%s' ",
                                td.getSchemaName(), td.getTableCode(), cd.getFieldName(),
                                cd.getRemarks().replace("\"", "\\\"")));
            }
        }

        return results;
    }

}
